import tkinter as tk
from tkinter import messagebox, filedialog ,simpledialog
import pandas as pd
from numpy import array
import pyperclip as py


def select_file():
    file_path = filedialog.askopenfilename()
    fileName.insert(tk.END, file_path)


def concat(filename):
    xls = pd.ExcelFile(filename)
    # 读取所有sheet的内容，并将它们存储在一个字典中
    sheets_dict = {}
    for sheet_name in xls.sheet_names:
        sheets_dict[sheet_name] = xls.parse(sheet_name)

    # 按行拼接这些DataFrame
    merged_df = pd.concat(sheets_dict.values(), ignore_index=True)

    # 将合并后的DataFrame写入到一个新的Excel文件中
    with pd.ExcelWriter(filename) as writer:
        merged_df.to_excel(writer, index=False)

    messagebox.showinfo("提示", "已合并所有工作簿到sheet1")

    df = pd.read_excel(filename)

    df['标志'] = None
    df.to_excel(filename, index=False, engine='openpyxl')


def read_execl(filename, name, passwd, number):
    try:
        df = pd.read_excel(filename,dtype=str)

        msg = df.loc[df['姓名'] == name]

        lens = len(msg.index.values)

        user_input = 1
        if lens > 1:
            messagebox.showinfo("提示", msg.to_string())
            user_input = simpledialog.askstring("输入框", "请选择标记第几个学生从上到下为1~N", initialvalue='1')
            while int(user_input) < 1 or int(user_input) > lens:
                user_input = simpledialog.askstring("重新输入", "请选择标记第几个学生从上到下为1~N")

        index = msg.index.values[int(user_input)-1]

        ar = array(msg).tolist()[0]

        #df.iat[index, 4] = 1

        df.at[index, '标志'] = 1

        account = '账号：' + str(ar[int(number)-1])
        miMa = '密码：' + passwd
        # 将数据写入文本框
        text_box.delete(1.0, tk.END)
        text_box.insert(tk.END, account + '\r\n')

        text_box.insert(tk.END, miMa + '\r\n' + '\r\n')
        text_box.insert(tk.END, huaShu.get())

        py.copy(account + '\r\n' + miMa + '\r\n' + '\r\n'+huaShu.get())

        messagebox.showinfo("提示", name + "已标记,回复内容已经复制到剪贴板")

        df.to_excel(filename, index=False, engine='openpyxl')
    except Exception:
       messagebox.showinfo("提示", "在文件中未找到" + name + "请确认学生姓名")


root = tk.Tk()
root.title('1.注意点击搜索按钮时，让表格保持关闭状态 2.表格使用前先备份原数据')

label1 = tk.Label(root, text='文件名：')
label1.grid(row=0, column=0, padx=10, pady=10)

fileName = tk.Entry(root, width=40)
fileName.grid(row=0, column=1, padx=5, pady=5)

chooseFile = tk.Button(root, text="选择文件", command=lambda: select_file())
chooseFile.grid(row=0, column=2, padx=5, pady=5)

chooseFile = tk.Button(root, text="合并文件", command=lambda: concat(fileName.get()))
chooseFile.grid(row=0, column=3, padx=5, pady=5)

number_label = tk.Label(root, text='账号处于文件的第几列：')
number_label.grid(row=1, column=0, padx=10, pady=10)

num = tk.Entry(root, width=40)
num.grid(row=1, column=1, padx=10, pady=10)

label2 = tk.Label(root, text='密码：')
label2.grid(row=2, column=0, padx=10, pady=10)

password = tk.Entry(root, width=40)
password.insert(0, "abc123")
password.grid(row=2, column=1, padx=5, pady=5)

label3 = tk.Label(root, text='学生姓名：')
label3.grid(row=3, column=0, padx=10, pady=10)

studentName = tk.Entry(root, width=40)
studentName.grid(row=3, column=1, padx=5, pady=5)

label3 = tk.Label(root, text='话术：')
label3.grid(row=4, column=0, padx=10, pady=10)

huaShu = tk.Entry(root, width=40)
huaShu.grid(row=4, column=1, padx=5, pady=5)

text_box = tk.Text(root)
text_box.grid(row=6, column=1, padx=5, pady=5)

button = tk.Button(root, text='搜索并标记',
                   command=lambda: read_execl(fileName.get(), studentName.get(), password.get(), num.get()))
button.grid(row=5, column=1, padx=5, pady=5)

root.mainloop()
